﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetMyMail]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetMyMail];
GO
CREATE PROCEDURE [dbo].[sproc_GetMyMail]
    @UserName nvarchar(255),
    @DisplayType int
/*

============================================================
功能:    得到我的邮件列表
参数:
    @UserName nvarchar(255)    :    用户名
    @DisplayType int        :    显示列表类型
============================================================

*/
As
BEGIN

--得到所有我的邮件列表
--0    我的收件
--1    我的发件
--2    我的回收
IF @DisplayType=0
    SELECT a.DocID,a.DocTitle,a.DocContent,c.readed,c.sender,a.DocAddedDate,c.receiver,b.Email,(SELECT count(*) FROM uds_files k WHERE k.docid = a.docid) as Attachs,D.classname,d.classid
        FROM
            UDS_Document a
            left outer join UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join UDS_MailAttach c on a.DocID = c.DocID
            left outer join UDS_CLASS D on A.CLASSID = D.CLASSID
        WHERE
            c.receiver = @username
            and a.DocType = 1
            and a.DocDeletion = 0
            and c.position = 1
        ORDER BY a.DocAddedDate DESC

IF @DisplayType=1
    SELECT a.DocID,a.DocTitle,a.DocContent,c.readed,c.allreceiver,a.DocAddedDate,c.sender,b.Email,(SELECT count(*) FROM uds_files k WHERE k.docid = a.docid) as Attachs,D.classname,d.classid
        FROM
            UDS_Document a
            left outer join UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join UDS_MailAttach c on a.DocID = c.DocID
            left outer join UDS_CLASS D on A.CLASSID = D.CLASSID
        WHERE
            c.sender = @username
            and a.DocType = 1
            and a.DocDeletion = 0
            and c.position = 2
        ORDER BY a.DocAddedDate DESC

IF @DisplayType=2
    SELECT a.DocID,a.DocTitle,a.DocContent,c.readed,c.sender,a.DocAddedDate,c.allreceiver,b.Email,(SELECT count(*) FROM uds_files k WHERE k.docid = a.docid) as Attachs,D.classname,d.classid
        FROM
            UDS_Document a
            left outer join UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join UDS_MailAttach c on a.DocID = c.DocID
            left outer join UDS_CLASS D on A.CLASSID = D.CLASSID
        WHERE
            (c.Sender = @username or c.receiver = @username)
            and a.DocType = 1
            and c.position = 3
        ORDER BY a.DocAddedDate DESC

IF @DisplayType=4
    SELECT a.DocID,a.DocTitle,a.DocContent,c.readed,c.sender,a.DocAddedDate,c.receiver,b.Email,(SELECT count(*) FROM uds_files k WHERE k.docid = a.docid) as Attachs,D.classname,d.classid
        FROM
            UDS_Document a
            left outer join UDS_STAFF b on a.DocAddedBy = b.Staff_Name
            inner join UDS_MailAttach c on a.DocID = c.DocID
            left outer join UDS_CLASS D on A.CLASSID = D.CLASSID
        WHERE
            c.receiver = @username
            and a.DocType = 1
            and a.DocDeletion = 0
            and c.position = 1
        ORDER BY a.DocAddedDate DESC,c.readed

END
GO